USE [master];
GO
CREATE DATABASE GradingSystemDb
GO

USE [GradingSystemDb]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EmployeeTypes] (
	[EmployeeTypeId] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](max) NOT NULL,
	[Description] [nvarchar](max) NOT NULL,
	[CreatedBy] [int] NOT NULL,
	[CreatedDttm] [datetime] NOT NULL,
	[ModifiedBy] [int] NOT NULL,
	[ModifiedDttm] [datetime] NOT NULL,
	CONSTRAINT [PK_EmployeeTypes] PRIMARY KEY CLUSTERED (
		[EmployeeTypeId] ASC
	)
	WITH (
		PAD_INDEX=OFF, 
		STATISTICS_NORECOMPUTE=OFF, 
		IGNORE_DUP_KEY=OFF, 
		ALLOW_ROW_LOCKS=ON, 
		ALLOW_PAGE_LOCKS=ON
	) 
	ON [PRIMARY]
) 
ON [PRIMARY]

USE [GradingSystemDb]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ExaminationTypes] (
	[ExaminationTypeId] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](max) NOT NULL,
	[Description] [nvarchar](max) NOT NULL,
	[GroupId] [int] NOT NULL,
	[CreatedBy] [int] NOT NULL,
	[CreatedDttm] [datetime] NOT NULL,
	[ModifiedBy] [int] NOT NULL,
	[ModifiedDttm] [datetime] NOT NULL,
	CONSTRAINT [PK_ExaminationTypes] PRIMARY KEY CLUSTERED (
		[ExaminationTypeId] ASC
	)
	WITH (
		PAD_INDEX=OFF, 
		STATISTICS_NORECOMPUTE=OFF, 
		IGNORE_DUP_KEY=OFF, 
		ALLOW_ROW_LOCKS=ON, 
		ALLOW_PAGE_LOCKS=ON
	) 
	ON [PRIMARY]
) 
ON [PRIMARY]

USE [GradingSystemDb]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Courses] (
	[CourseId] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](max) NOT NULL,
	[Description] [nvarchar](max) NOT NULL,
	[Code] [nvarchar](max) NOT NULL,
	[CreatedBy] [int] NOT NULL,
	[CreatedDttm] [datetime] NOT NULL,
	[ModifiedBy] [int] NOT NULL,
	[ModifiedDttm] [datetime] NOT NULL,
	CONSTRAINT [PK_Courses] PRIMARY KEY CLUSTERED (
		[CourseId] ASC
	)
	WITH (
		PAD_INDEX=OFF, 
		STATISTICS_NORECOMPUTE=OFF, 
		IGNORE_DUP_KEY=OFF, 
		ALLOW_ROW_LOCKS=ON, 
		ALLOW_PAGE_LOCKS=ON
	) 
	ON [PRIMARY]
) 
ON [PRIMARY]

USE [GradingSystemDb]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Subjects] (
	[SubjectId] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](max) NOT NULL,
	[Description] [nvarchar](max) NOT NULL,
	[Code] [nvarchar](max) NOT NULL,
	[Units] [decimal](6,2) NOT NULL,
	[IsActive] [bit] NOT NULL,
	[CreatedBy] [int] NOT NULL,
	[CreatedDttm] [datetime] NOT NULL,
	[ModifiedBy] [int] NOT NULL,
	[ModifiedDttm] [datetime] NOT NULL,
	CONSTRAINT [PK_Subjects] PRIMARY KEY CLUSTERED (
		[SubjectId] ASC
	)
	WITH (
		PAD_INDEX=OFF, 
		STATISTICS_NORECOMPUTE=OFF, 
		IGNORE_DUP_KEY=OFF, 
		ALLOW_ROW_LOCKS=ON, 
		ALLOW_PAGE_LOCKS=ON
	) 
	ON [PRIMARY]
) 
ON [PRIMARY]

USE [GradingSystemDb]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employees] (
	[EmployeeId] [int] IDENTITY(1,1) NOT NULL,
	[CourseId] [int] NOT NULL,
	[EmployeeTypeId] [int] NOT NULL,
	[FirstName] [nvarchar](max) NOT NULL,
	[MiddleName] [nvarchar](max) NOT NULL,
	[LastName] [nvarchar](max) NOT NULL,
	[HomeAddress] [nvarchar](max) NOT NULL,
	[ContactNumber] [nvarchar](max) NOT NULL,
	[EmailAddress] [nvarchar](max) NOT NULL,
	[EmployeeCode] [nvarchar](max) NOT NULL,
	[Year] [int] NOT NULL,
	[IsActive] [bit] NOT NULL,
	[CreatedBy] [int] NOT NULL,
	[CreatedDttm] [datetime] NOT NULL,
	[ModifiedBy] [int] NOT NULL,
	[ModifiedDttm] [datetime] NOT NULL,
	CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED (
		[EmployeeId] ASC
	)
	WITH (
		PAD_INDEX=OFF, 
		STATISTICS_NORECOMPUTE=OFF, 
		IGNORE_DUP_KEY=OFF, 
		ALLOW_ROW_LOCKS=ON, 
		ALLOW_PAGE_LOCKS=ON
	) 
	ON [PRIMARY]
) 
ON [PRIMARY]

GO
ALTER TABLE	[dbo].[Employees] WITH CHECK ADD CONSTRAINT [FK_Courses_CourseId_Employees_CourseId] FOREIGN KEY([CourseId])
REFERENCES  [dbo].[Courses] ([CourseId])
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_Courses_CourseId_Employees_CourseId]
GO
ALTER TABLE	[dbo].[Employees] WITH CHECK ADD CONSTRAINT [FK_EmployeeTypes_EmployeeTypeId_Employees_EmployeeTypeId] FOREIGN KEY([EmployeeTypeId])
REFERENCES  [dbo].[EmployeeTypes] ([EmployeeTypeId])
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_EmployeeTypes_EmployeeTypeId_Employees_EmployeeTypeId]

USE [GradingSystemDb]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Users] (
	[UserId] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeId] [int] NOT NULL,
	[Username] [nvarchar](max) NOT NULL,
	[Password] [nvarchar](max) NOT NULL,
	[CreatedBy] [int] NOT NULL,
	[CreatedDttm] [datetime] NOT NULL,
	[ModifiedBy] [int] NOT NULL,
	[ModifiedDttm] [datetime] NOT NULL,
	CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED (
		[UserId] ASC
	)
	WITH (
		PAD_INDEX=OFF, 
		STATISTICS_NORECOMPUTE=OFF, 
		IGNORE_DUP_KEY=OFF, 
		ALLOW_ROW_LOCKS=ON, 
		ALLOW_PAGE_LOCKS=ON
	) 
	ON [PRIMARY]
) 
ON [PRIMARY]

GO
ALTER TABLE	[dbo].[Users] WITH CHECK ADD CONSTRAINT [FK_Employees_EmployeeId_Users_EmployeeId] FOREIGN KEY([EmployeeId])
REFERENCES  [dbo].[Employees] ([EmployeeId])
GO
ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_Employees_EmployeeId_Users_EmployeeId]


USE [GradingSystemDb]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Schedules] (
	[ScheduleId] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeId] [int] NOT NULL,
	[SubjectId] [int] NOT NULL,
	[RoomNumber] [nvarchar](max) NOT NULL,
	[ScheduleDay] [nvarchar](max) NOT NULL,
	[StartTime] [datetime] NOT NULL,
	[EndTime] [datetime] NOT NULL,
	[StartDate] [datetime] NOT NULL,
	[EndDate] [datetime] NOT NULL,
	[Semester] [nvarchar](max) NOT NULL,
	[CreatedBy] [int] NOT NULL,
	[CreatedDttm] [datetime] NOT NULL,
	[ModifiedBy] [int] NOT NULL,
	[ModifiedDttm] [datetime] NOT NULL,
	CONSTRAINT [PK_Schedules] PRIMARY KEY CLUSTERED (
		[ScheduleId] ASC
	)
	WITH (
		PAD_INDEX=OFF, 
		STATISTICS_NORECOMPUTE=OFF, 
		IGNORE_DUP_KEY=OFF, 
		ALLOW_ROW_LOCKS=ON, 
		ALLOW_PAGE_LOCKS=ON
	) 
	ON [PRIMARY]
) 
ON [PRIMARY]

GO
ALTER TABLE	[dbo].[Schedules] WITH CHECK ADD CONSTRAINT [FK_Employees_EmployeeId_Schedules_EmployeeId] FOREIGN KEY([EmployeeId])
REFERENCES  [dbo].[Employees] ([EmployeeId])
GO
ALTER TABLE [dbo].[Schedules] CHECK CONSTRAINT [FK_Employees_EmployeeId_Schedules_EmployeeId]
GO
ALTER TABLE [dbo].[Schedules]  WITH CHECK ADD  CONSTRAINT [FK_Subjects_SubjectId_Schedules_SubjectId] FOREIGN KEY([SubjectId])
REFERENCES  [dbo].[Subjects] ([SubjectId])
GO
ALTER TABLE [dbo].[Schedules] CHECK CONSTRAINT [FK_Subjects_SubjectId_Schedules_SubjectId]

USE [GradingSystemDb]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Matriculations] (
	[EmployeeId] [int] NOT NULL,
	[ScheduleId] [int] NOT NULL,
	[GradePoint] [decimal](6, 2) NOT NULL CONSTRAINT [DF_Matriculations_GradePoint] DEFAULT ((7.00)),
	[CreatedBy] [int] NOT NULL,
	[CreatedDttm] [datetime] NOT NULL,
	[ModifiedBy] [int] NOT NULL,
	[ModifiedDttm] [datetime] NOT NULL
) 
ON [PRIMARY]

GO
ALTER TABLE	[dbo].[Matriculations] WITH CHECK ADD CONSTRAINT [FK_Employees_EmployeeId_Matriculations_EmployeeId] FOREIGN KEY([EmployeeId])
REFERENCES  [dbo].[Employees] ([EmployeeId])
GO
ALTER TABLE [dbo].[Matriculations] CHECK CONSTRAINT [FK_Employees_EmployeeId_Matriculations_EmployeeId]
GO
ALTER TABLE	[dbo].[Matriculations] WITH CHECK ADD CONSTRAINT [FK_Schedules_ScheduleId_Matriculations_ScheduleId] FOREIGN KEY([ScheduleId])
REFERENCES  [dbo].[Schedules] ([ScheduleId])
GO
ALTER TABLE [dbo].[Matriculations] CHECK CONSTRAINT [FK_Schedules_ScheduleId_Matriculations_ScheduleId]

USE [GradingSystemDb]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Examinations] (
	[ExaminationId] [int] IDENTITY(1,1) NOT NULL,
	[ScheduleId] [int] NOT NULL,
	[GroupId] [int] NOT NULL,
	[StudentCode] [nvarchar](max) NOT NULL,
	[ExaminationTypeId] [int] NOT NULL,
	[Grade] [decimal](6,2) NOT NULL,
	[CreatedBy] [int] NOT NULL,
	[CreatedDttm] [datetime] NOT NULL,
	[ModifiedBy] [int] NOT NULL,
	[ModifiedDttm] [datetime] NOT NULL,
	CONSTRAINT [PK_Examinations] PRIMARY KEY CLUSTERED (
		[ExaminationId] ASC
	)
	WITH (
		PAD_INDEX=OFF, 
		STATISTICS_NORECOMPUTE=OFF, 
		IGNORE_DUP_KEY=OFF, 
		ALLOW_ROW_LOCKS=ON, 
		ALLOW_PAGE_LOCKS=ON
	) 
	ON [PRIMARY]
) 
ON [PRIMARY]

GO
ALTER TABLE	[dbo].[Examinations] WITH CHECK ADD CONSTRAINT [FK_Schedules_ScheduleId_Examinations_ScheduleId] FOREIGN KEY([ScheduleId])
REFERENCES  [dbo].[Schedules] ([ScheduleId])
GO
ALTER TABLE [dbo].[Examinations] CHECK CONSTRAINT [FK_Schedules_ScheduleId_Examinations_ScheduleId]
GO
ALTER TABLE	[dbo].[Examinations] WITH CHECK ADD CONSTRAINT [FK_ExaminationTypes_ExaminationTypeId_Examinations_ExaminationTypeId] FOREIGN KEY([ExaminationTypeId])
REFERENCES  [dbo].[ExaminationTypes] ([ExaminationTypeId])
GO
ALTER TABLE [dbo].[Examinations] CHECK CONSTRAINT [FK_ExaminationTypes_ExaminationTypeId_Examinations_ExaminationTypeId]
GO
ALTER TABLE	[dbo].[Examinations] WITH CHECK ADD CONSTRAINT [FK_ExaminationTypes_ExaminationTypeId_Examinations_GroupId] FOREIGN KEY([GroupId])
REFERENCES  [dbo].[ExaminationTypes] ([ExaminationTypeId])
GO
ALTER TABLE [dbo].[Examinations] CHECK CONSTRAINT [FK_ExaminationTypes_ExaminationTypeId_Examinations_GroupId]

USE [master];
GO
CREATE LOGIN [SouthdaleUser] WITH PASSWORD='$outhdal3', DEFAULT_DATABASE=[GradingSystemDb], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON;
GO

USE [GradingSystemDb];
GO
CREATE USER [SouthdaleUser] FOR LOGIN [SouthdaleUser] WITH DEFAULT_SCHEMA=[dbo];
GO
GRANT INSERT, SELECT, UPDATE, DELETE TO [SouthdaleUser]
GO

